<?php

/**
 * Created by PhpStorm.
 * User: z
 * Date: 14-8-6
 * Time: 上午9:36
 */
class Warningtmp
{
    /**
     * 刷新sku到数据库
     * @param $all
     * @return array
     */
    static function flush_skus($all = FALSE)
    {
        $skus       = Warningtmp::get_sku($all); //echo "#skuall=>".count($skus);
        $exist_skus = Warningtmp::get_exist_skus();

        /**
         * 分别得到
         * 需要插入的数据
         * 需要更新的数据
         */
        $insert_skus = array_diff($skus,$exist_skus);
        $update_skus = array_intersect($exist_skus,$skus);

        /**
         * 重置标示位信息
         */
        Warningtmp::reset_exist_skus_flag();

        /**
         * 执行插入和更新
         */
        Warningtmp::insert_skus($insert_skus);
        Warningtmp::update_exist_skus_flag($update_skus);

        /**
         * 更新在途天数/交期
         */
        Warningtmp::flush_delivery($skus);

        return $skus;
    }

    /**
     * 刷新在途天数/交期
     * @param $skus
     * @return array
     */
    static function flush_delivery(array $skus = NULL)
    {
        if($skus){
            $update_string = '';
            $sku_string    = '';

            $delivery = Warningtmp::get_delivery($skus);
            foreach($skus as $sku){
                //                $delivery = Warningtmp::get_sku_delivery($sku);
                $delivery_one = isset($delivery[$sku])?$delivery[$sku]:7;
                //                $update_string .= " WHEN '".$sku."' THEN '".$delivery."'";
                $update_string .= " WHEN '".$sku."' THEN '".$delivery_one."'";

                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }

            $update_string = " UPDATE warning_tmp SET delivery= CASE sku ".$update_string." END AND sku in (".$sku_string.")";
            //echo $update_string;exit;
            list($id,$rows) = DB::query(Database::UPDATE,$update_string)
                ->execute('newerp_tmp');

            return $rows;
        }
    }

    /**
     * 获取计算过了的sku
     * @return array
     */
    static function get_exist_skus_flag()
    {
        $sql_string = 'SELECT upper(sku) as sku FROM warning_tmp where flag = 0';
        $skus       = DB::query(Database::SELECT,$sql_string)
            ->execute('newerp_tmp')
            ->as_array();

        if($skus){
            $skus = ARR::pluck($skus,'sku');
        }

        return $skus;
    }

    /**
     * 重置零时表的标志位为-1

     */
    static function reset_exist_skus_flag()
    {
        $sql_string = "UPDATE warning_tmp set flag = -1";

        list($id,$rows) = DB::query(Database::UPDATE,$sql_string)
            ->execute('newerp_tmp');

        return $rows;
    }

    /**
     * 更新原表中存在的ｓｋｕ的标示位
     * @param array $skus
     */
    static function update_exist_skus_flag(array $skus)
    {
        if($skus){
            $countskus = count($skus);
            for($i = 0;$i<$countskus;$i += 10000){
                $skus_slice  = array_slice($skus,$i,10000);
                $skus_string = "";
                foreach($skus_slice as $sku){
                    $skus_string .= $skus_string?",'".$sku."'":"'".$sku."'";
                }
                $sql_string = "UPDATE warning_tmp set flag = 0 where sku in (".$skus_string.")"; //echo $sql_string;exit;
                DB::query(Database::UPDATE,$sql_string)
                    ->execute('newerp_tmp');
            }
        }
    }

    /**
     * 插入在原表中不存在的ｓｋｕ信息,并且设置标示位
     * @param array $skus
     */
    static function insert_skus(array $skus)
    {
        if($skus){
            $countskus = count($skus);
            for($i = 0;$i<$countskus;$i += 10000){
                $skus_slice = array_slice($skus,$i,10000);
                $sku_string = '';
                foreach($skus_slice as $sku){
                    $sku_string .= $sku_string?",('".$sku."',0)":"('".$sku."',0)";
                }
                $sql_string = "INSERT INTO warning_tmp(sku, flag)VALUES ";
                //echo $sql_string.$sku_string;exit;
                DB::query(Database::INSERT,$sql_string.$sku_string)
                    ->execute('newerp_tmp');
            }
        }
    }

    /**
     * 查询零时表中已近存在的ｓｋｕ信息
     * @return array
     */
    static function get_exist_skus()
    {
        $sql_string = 'SELECT upper(sku) as sku FROM warning_tmp ';
        $skus       = DB::query(Database::SELECT,$sql_string)
            ->execute('newerp_tmp')
            ->as_array();

        if($skus){
            $skus = ARR::pluck($skus,'sku');
        }

        return $skus;
    }

    /**
     * 查询全部的在售的并且有销量的sku
     * 可以查询全部的sku的信息
     * @param $all
     * @return array
     */
    static function get_sku($all = FALSE)
    {
        /**
         * products_remark_2 在售状态
         * 下架：0
         * 在售：1
         * 新品测试：2
         * 暂时下架：3
         * 清货下架：4
         * 知识产权下架：5
         * 速卖通出售：6
         * 淘宝出售：7
         * 阿里出售：8
         * 工厂包材：10
         */
        $salestatus = '1,2,3,6,7';
        if(isset($all['salestatus'])){
            if(is_array($all['salestatus'])){
                $salestatus = implode(',',$all['salestatus']);
            }elseif(is_string($all['salestatus'])){
                $salestatus = $all['salestatus'];
            }
        }
        $salestatus = $salestatus?$salestatus:'1,2,3,6,7';

        //      -------------------------------------kdw---------------------------------------
        //        $sql_string1 = "select upper(products_sku) as sku
        //			from erp_products_data
        //			where products_remark_2 in (".$salestatus.")
        //			and ifnull(products_sku,'') <> ''
        //			order by products_sku";
        //        /*
        //         * echo $sql_string1;
        //         */
        //        $sku_sales = DB::query(Database::SELECT,$sql_string1)
        //            ->execute('readonlyerp')
        //            ->as_array();

        $sku_sales=Skusystemapi::getSalesku();
        //      -------------------------------------kdw-end-------------------------------------


        if($sku_sales and isset($sku_sales[0]['sku'])){
            $sku_sales = Arr::pluck($sku_sales,'sku');
        }

        /**
         * 销量大于０
         * 并且需要预警
         */
        /**
         * 销量默认为大于0
         */
        $daysale = 0;
        if(isset($all['daysale'])){
            $daysale = (float)$all['daysale'];
        }

        /**
         * 预警状态默认为需要预警的
         * warning_status
         * 0
         * 1
         */
        $warningstatus = '0';
        if(isset($all['warningstatus'])){
            if(is_array($all['warningstatus'])){
                $warningstatus = implode(',',$all['warningstatus']);
            }elseif(is_string($all['warningstatus'])){
                $warningstatus = $all['warningstatus'];
            }
        }
        $warningstatus = $warningstatus?$warningstatus:'0';

        $sql_string2  = "select upper(sku) as sku
         from product_attached
         where warning_status in (".$warningstatus.")
         and ifnull(sku,'') <> ''
         and daysales>".$daysale;
        $sku_daysales = DB::query(Database::SELECT,$sql_string2)
            ->execute('readonlyerp')
            ->as_array();
        if($sku_daysales and isset($sku_daysales[0]['sku'])){
            $sku_daysales = Arr::pluck($sku_daysales,'sku');
        }

        /**
         * sku有供应商可用的
         * 供应商状态erp_provider.state
         * 0新录入
         * 1未通过
         * 2已通过
         * 供应商状态为已通过

         */
        $providerstatus = '2';
        if(isset($all['providerstatus'])){
            if(is_array($all['providerstatus'])){
                $providerstatus = implode(',',$all['providerstatus']);
            }elseif(is_string($all['providerstatus'])){
                $providerstatus = $all['providerstatus'];
            }
        }
        //      ---------------------------------------kdw-----------------------------------
        //        $sql_string3   = "SELECT
        //            trim(erp_providerproducts.iproductID) as sku
        //            from erp_providerproducts
        //            join erp_provider
        //            on erp_providerproducts.iproviderID=erp_provider.id
        //            where trim(erp_providerproducts.iproductID) <> ''
        //            and erp_provider.state in(".$providerstatus.")";
        //        $sku_providers = DB::query(Database::SELECT,$sql_string3)
        //            ->execute('readonlyerp')
        //            ->as_array();

        $sku_providers=Newerpclientapi::querySuppliersku();
        //      --------------------------------------kdw-end--------------------------------

        if($sku_providers and isset($sku_providers[0]['sku'])){
            $sku_providers = Arr::pluck($sku_providers,'sku');
        }

        /**
         * 合并符合条件的sku
         */
        $skus = array_intersect($sku_sales,$sku_daysales);
        $skus = array_intersect($skus,$sku_providers);

        return $skus;
    }

    /**
     * 计算实际库存量
     * @param array sku
     * @return array
     */
    static function get_inventory(array $skus = NULL)
    {
        if($skus){
            $skus_string = "";
            foreach($skus as $sku){
                $skus_string .= $skus_string?",'".$sku."'":"'".$sku."'";
            }

            $date = date('Y-m-d');;
            $sql_string = "select upper(sku) as sku,sum(num) as inventory from (
	SELECT
		warehouseorders.sku AS sku,
		warehouseorders.warehouse_quantity AS num
	FROM
		warehouseorders
	WHERE
		warehouseorders.warehouse_date >= DATE_FORMAT('".$date."', '%Y-%m-01')
    UNION ALL
		SELECT
			`deliveryorders`.`sku` AS `sku` ,- (
				`deliveryorders`.`quantity`
			) AS `num`
		FROM
			`deliveryorders`
		WHERE
			`deliveryorders`.`delivery_date` >= DATE_FORMAT('".$date."', '%Y-%m-01')

    UNION ALL

		SELECT
			`inventories`.`sku` AS `sku`,
			`inventories`.`quantity` AS `num`
		FROM
			`inventories`
		WHERE
			`inventories`.`inventory_date` = DATE_FORMAT('".$date."', '%Y-%m-01')
	) a where sku in (".$skus_string.") GROUP BY sku";

            //echo $sql_string;exit;

            $re = DB::query(Database::SELECT,$sql_string)
                ->execute('newerp')
                ->as_array();

            $inventory = array();
            if($re){
                foreach($re as $value){
                    if(!empty($value['sku']))
                        $inventory[trim($value['sku'])] = $value['inventory'];
                }
            }

            return $inventory;
        }
    }

    static function get_inventory_for_vmi(array $skus = NULL, $warehouseid, $enterprise_dominant) {
        if ($skus) {
            $skus_string = "";
            foreach($skus as $sku){
                $skus_string .= $skus_string?",'".$sku."'":"'".$sku."'";
            }

            $date = date('Y-m-d');;
            $sql_string = "select upper(sku) as sku,sum(num) as inventory from (
            SELECT
                warehouseorders.sku AS sku,
                warehouseorders.warehouse_quantity AS num
            FROM
                warehouseorders
            WHERE
                warehouseorders.warehouse_date >= DATE_FORMAT('".$date."', '%Y-%m-01')
            AND warehouseorders.warehouseid = {$warehouseid} 
            AND warehouseorders.enterprise_dominant IN({$enterprise_dominant},0)
        UNION ALL
            SELECT
                `deliveryorders`.`sku` AS `sku` ,- (
                    `deliveryorders`.`quantity`
                ) AS `num`
		    FROM
			    `deliveryorders`
		    WHERE
			    `deliveryorders`.`delivery_date` >= DATE_FORMAT('".$date."', '%Y-%m-01')
			AND `deliveryorders`.`warehouseid` = {$warehouseid} 
			AND `deliveryorders`.`enterprise_dominant` IN({$enterprise_dominant},0)
        UNION ALL
            SELECT
                `sku_stocks`.`sku` AS `sku`,
                `sku_stocks`.`stock` AS `num`
            FROM
                `sku_stocks`
            WHERE
                `sku_stocks`.`date` = '".$date."'
            AND `sku_stocks`.`warehouseid` = {$warehouseid}
            AND `sku_stocks`.`enterprise_dominant` IN({$enterprise_dominant},0)
	) a where sku in (".$skus_string.") GROUP BY sku";

            //echo $sql_string;exit;

            $re = DB::query(Database::SELECT,$sql_string)
                ->execute('newerp')
                ->as_array();

            $inventory = array();
            if($re){
                foreach($re as $value){
                    if(!empty($value['sku']))
                        $inventory[trim($value['sku'])] = $value['inventory'];
                }
            }

            return $inventory;
        }
    }


    /**
     * 计算订单占用库存
     * @param array sku
     * @return array
     */
    static function get_print_num(array $skus = NULL)
    {
        if($skus){
            $sku_string = '';
            foreach($skus as $sku){
                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }

            /**
             * 订单占用库存选取状态
             * erp_orders.orders_status
             * 1新录入
             * 3已通过
             * 4已打印
             * 8缺货
             */
            //20150113 gqc 添加　and eo.orders_type NOT IN (41,42,49,44)
            //20161107朱自立增加抓下来的订单即开始计算
            $sql_string = "SELECT upper(erp_orders_products.orders_sku) AS sku,
					SUM( erp_orders_products.item_count ) AS printnum
					FROM erp_orders_products
					JOIN erp_orders ON erp_orders_products.erp_orders_id = erp_orders.erp_orders_id
					WHERE erp_orders_products.orders_sku IN (".$sku_string.")
					AND erp_orders.orders_type NOT IN (-2,-1,41,42,44,46,47,49,104,105,421,422,423,428)
					AND erp_orders.orders_status IN (1,2,3,4,8,9,102)
					AND erp_orders.erp_op_id>=0
					GROUP BY erp_orders_products.orders_sku";

            $re = DB::query(Database::SELECT,$sql_string)
                ->execute('readonlyerp')
                ->as_array('sku','printnum');
            return $re;
            //            return $printnum;
        }
    }

    /**
     * 计算在途库存信息
     * @param array $skus
     * @return array
     */
    static function get_onway_num(array $skus = NULL)
    {
        if($skus){
            $sku_string = '';
            foreach($skus as $sku){
                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }

            $sql_string = 'select  upper(sku) as sku,SUM(quantity-ware_quantity) as owaynum
                            from  purchaseorder_details  where sku IN ('.$sku_string.')
                            and  status<70
                            and  details_date >= DATE_ADD(NOW(),INTERVAL -90 DAY) group by sku';
            //echo $sql_string;exit;
            $re       = DB::query(Database::SELECT,$sql_string)
                ->execute('newerp')
                ->as_array();
            $onwaynum = array();
            if($re){
                foreach($re as $value){
                    $onwaynum[$value['sku']] = $value['owaynum'];
                }
            }
            
            return $onwaynum;
        }
    }

    /**
     * 查询安全库存
     * @param array $skus
     * @return array
     */
    static function get_safenum(array $skus = NULL)
    {
        if($skus){
            $sku_string = '';
            foreach($skus as $sku){
                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }

            $sql_string = 'select upper(sku) as sku,wave_days as safenum from product_attached where sku in ('.$sku_string.')';
            //echo $sql_string;exit;
            $re      = DB::query(Database::SELECT,$sql_string)
                ->execute('readonlyerp')
                ->as_array();
            $safenum = array();
            if($re){
                foreach($re as $value){
                    $safenum[$value['sku']] = $value['safenum'];
                }
            }

            return $safenum;
        }
    }

    /**
     * 查询日均销量
     * @param array $skus
     * @return array
     */
    static function get_daysalenum(array $skus = NULL)
    {
        if($skus){
            $sku_string = '';
            foreach($skus as $sku){
                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }
            $sql_string = 'select upper(sku) as sku,daysales as daysalenum from product_attached where sku in ('.$sku_string.')';
            //echo $sql_string;exit;
            $re         = DB::query(Database::SELECT,$sql_string)
                ->execute('readonlyerp')
                ->as_array();
            $daysalenum = array();
            if($re){
                foreach($re as $value){
                    $daysalenum[$value['sku']] = $value['daysalenum'];
                }
            }

            return $daysalenum;
        }
    }

    /**
     * 获取在途天数／交期
     * @param null $skus
     * @return array
     */
    static function get_delivery($skus = NULL)
    {
        if($skus){
            $delivery = array();

            $sku_string = '';
            foreach($skus as $sku){
                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }

            /**
             * 全部的sku对应的供应商信息
             */
            //          -----------------------------------------kdw------------------------------------
            //            $providers_string = "SELECT
            //            trim(erp_providerproducts.iproductID) as sku,
            //            erp_providerproducts.iproviderID as suppid ,
            //            erp_providerproducts.price as price,
            //            erp_providerproducts.delivery as delivery
            //            from erp_providerproducts
            //            join erp_provider
            //            on erp_providerproducts.iproviderID=erp_provider.id
            //            where erp_provider.state=2
            //            and trim(erp_providerproducts.iproductID) in (".$sku_string.")
            //            order by trim(erp_providerproducts.iproductID),erp_providerproducts.price";
            //            //echo $providers_string;exit;
            //            $providers = DB::query(Database::SELECT,$providers_string)
            //                ->execute('readonlyerp')
            //                ->as_array();

            $providers=Newerpclientapi::querySupplierSkuBySkus($skus);
            //          ------------------------------------------kdw-end---------------------------------
            if(empty($providers)){
                return;
            }

            /**
             * 最近一次的sku的采购的供应商信息
             */
            $purchaseorders_string = "SELECT upper(sku) as sku,supplier as suppid
            from  purchaseorder_details  where sku in (".$sku_string.")
            and  status<70
            and  details_date >= DATE_ADD(NOW(),INTERVAL -90 DAY)
            group by sku
            order by details_date DESC ";

            $purchaseorders = DB::query(Database::SELECT,$purchaseorders_string)
                ->execute('newerp')
                ->as_array();

            /**
             * 最近采购的供应商id
             * array(
             * sku=>suppid
             * )
             */
            $purchaseorders_arr = array();
            if($purchaseorders){
                foreach($purchaseorders as $purchaseorder){
                    $purchaseorders_arr[$purchaseorder['sku']] = $purchaseorder['suppid'];
                }
            }

            $providers_arr = array();
            if($providers){
                /**
                 * 0=>array(sku,suppid,price,delivery)
                 * 1=>array()
                 * 2=>array()
                 */
                foreach($providers as $provider){
                    /**
                     * 多个供应商，
                     * 比较价格
                     */
                    if(array_key_exists($provider['sku'],$delivery) and array_key_exists($provider['sku'],$providers_arr)){
                        /**
                         * 如果存在和最低价格一致
                         * 并且是最近采购单供应商
                         * 则使用该供应商的交期信息
                         */
                        if($provider['price']==$providers_arr[$provider['sku']] and $provider['suppid']==$purchaseorders_arr[$provider['sku']]){
                            $delivery[$provider['sku']] = $provider['delivery'];
                        }
                    }else{
                        /**
                         * 默认就是最低价的供应商
                         * 分别存储最低价格和交期
                         */
                        $providers_arr[$provider['sku']] = $provider['price'];

                        $delivery[$provider['sku']] = $provider['delivery'];
                    }
                }
            }
            //print_r($delivery);
            return $delivery;
        }
    }

    /**
     * 计算单个sku的交期值
     * @param null $sku
     * @return null
     */
    static function get_sku_delivery($sku = NULL)
    {

        //        if($sku){
        //            $sql_supp = " SELECT erp_providerproducts.iproviderID as suppid ,erp_providerproducts.delivery as delivery
        //            from erp_providerproducts
        //            join erp_provider
        //            on erp_providerproducts.iproviderID=erp_provider.id
        //            where erp_provider.state=2
        //            and trim(erp_providerproducts.iproductID)='".$sku."'
        //            and erp_providerproducts.price = (
        //            select min(erp_providerproducts.price)
        //            from erp_providerproducts
        //            join erp_provider
        //            on erp_providerproducts.iproviderID=erp_provider.id
        //            where erp_provider.state=2
        //            and trim(erp_providerproducts.iproductID)='".$sku."')";
        //
        //            $suppids = DB::query(Database::SELECT,$sql_supp)
        //                ->execute('readonlyerp')
        //                ->as_array();
        //
        //            $count = count($suppids);
        //            if($count<=0){
        //                return NULL;
        //            }elseif($count==1){
        //                return $suppids[0]['delivery'];
        //            }elseif($count>1){
        //                /**
        //                 * 最近一次采购的供应商
        //                 */
        //                $purchase_string = " SELECT supplier from  purchaseorder_details  where sku ='".$sku."'
        //                            and  status<70
        //                            and  details_date >= DATE_ADD(NOW(),INTERVAL -90 DAY)
        //                            order by details_date limit 1";
        //                $supplier        = DB::query(Database::SELECT,$purchase_string)
        //                    ->execute('newerp')
        //                    ->current();
        //
        //                if(isset($supplier['supplier'])){
        //                    foreach($suppids as $suppid){
        //                        if($suppid['suppid']==$supplier){
        //                            return $suppid['delivery'];
        //                        }
        //                    }
        //                }else{
        //                    return $suppids[0]['delivery'];
        //                }
        //            }
        //        }
    }

    /**
     * 获取在途天数／交期
     * @param null $skus
     * @return array
     */
    static function get_delivery_new($skus = NULL)
    {
        if($skus){
            $sku_string = '';
            foreach($skus as $sku){
                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }

            $sql_string = ' SELECT upper(sku) as sku,delivery FROM warning_tmp where sku in ('.$sku_string.')';

            $re = DB::query(Database::SELECT,$sql_string)
                ->execute('newerp_tmp')
                ->as_array();

            $delivery = array();
            if($re){
                foreach($re as $value){
                    $delivery[$value['sku']] = $value['delivery'];
                }
            }

            return $delivery;
        }
    }

    /**
     * 根据采购员id获取他负责的sku
     * @param null $oper
     * @return array
     */
    static function get_skus_by_oper($oper = NULL)
    {
        if($oper){
            //          ----------------------------------------kdw-----------------------------------------
            //            $sql_string = "SELECT upper(products_sku) as sku from  erp_products_data where oper = '".$oper."'";
            //            $re         = DB::query(Database::SELECT,$sql_string)
            //                ->execute('readonlyerp')
            //                ->as_array();
            $re=Skusystemapi::getUserAllSku($oper);
            //          ----------------------------------------kdw-----------------------------------------
            if($re){
                $skus = Arr::pluck($re,'sku');
                return $skus;
            }
        }
    }

    /**
     * 计算数据并且写入到数据库
     * @param null $skus
     * @return null array
     */
    static function count_waring_to_db($skus = NULL)
    {
        //echo count($skus);
        if(empty($skus)){
            return;
        }

        /**
         * 计算各种数据
         */
        $countskus  = count($skus); //sku总数
        $inventory  = Warningtmp::get_inventory($skus); //echo "#计算实际库存".count($inventory);
        $printnum   = Warningtmp::get_print_num($skus); //echo "#计算订单占用库存".count($printnum);
        $onwaynum   = Warningtmp::get_onway_num($skus); //echo "#计算在途库存".count($onwaynum);
        $safenum    = Warningtmp::get_safenum($skus); //echo "#计算安全库存".count($safenum);
        $daysalenum = Warningtmp::get_daysalenum($skus); //echo "#计算日均销量库存".count($daysalenum);

        /**
         * 计算在途天数耗时较多
         * 使用数据库存储的信息
         */
        $delivery = Warningtmp::get_delivery($skus);
        //        $delivery = Warningtmp::get_delivery_new($skus);//echo "#计算日均销量库存".count($delivery);

        /**
         * 更新数据到数据库
         */
        $update_string = ' UPDATE warning_tmp set ';
        for($i = 0;$i<$countskus;$i += 500){

            $sql_string_best   = '';
            $sql_string_time   = '';
            $daysalenum_string = '';
            $inventory_string  = '';
            $printnum_string   = '';
            $onwaynum_string   = '';
            $safenum_string    = '';
            $delivery_string   = '';

            $suppdays_string = '';

            $skus_string = '';

            $skus_slice = array_slice($skus,$i,500);

            foreach($skus_slice as $sku){

                //销量
                $sku_daysalenum = 0;
                if(isset($daysalenum[$sku])){
                    $sku_daysalenum = $daysalenum[$sku];
                    unset($daysalenum[$sku]);
                }

                /**
                 * 如果日均销量等于0
                 */
                if($sku_daysalenum<=0){
                    //continue;
                }

                //库存
                $sku_inventory = 0;
                if(isset($inventory[$sku])){
                    $sku_inventory = $inventory[$sku];
                    unset($inventory[$sku]);
                }

                //订单占用
                $sku_printnum = 0;
                if(isset($printnum[$sku])){
                    $sku_printnum = $printnum[$sku];
                    unset($printnum[$sku]);
                }

                //在途库存
                $sku_onwaynum = 0;
                if(isset($onwaynum[$sku])){
                    $sku_onwaynum = $onwaynum[$sku];
                    unset($onwaynum[$sku]);
                }

                /**
                 * 安全库存对应的波动天数
                 * 默认为3天
                 */
                $sku_safenum = 3;
                if(isset($safenum[$sku])){
                    $sku_safenum = $safenum[$sku];
                    unset($safenum[$sku]);
                }

                $sku_delivery = 7;
                if(isset($delivery[$sku])){
                    $sku_delivery = $delivery[$sku];
                    unset($delivery[$sku]);
                }

                /**
                 * 最佳下单日期和今天的差值
                 * 如果销量等于0，则可支持天数为90天，即三个月
                 */
                if($sku_daysalenum>0){
                    $suppdays = ((float)$sku_inventory-(float)$sku_printnum+(float)$sku_onwaynum)/((float)$sku_daysalenum)-(float)$sku_safenum;
                }else{
                    $suppdays = 90;
                }
                $best = (float)$suppdays-(float)$sku_delivery;
                /*                echo "#".$sku.
                                    "#".$sku_inventory."#". $sku_printnum."#".$sku_onwaynum."#".$sku_daysalenum."#".$sku_safenum.
                                    "#".$suppdays.
                                    "#".$sku_delivery.
                                    "#".$best;
                                echo "<br/>";*/
                /**
                 * 拼接sql语句用于批量更新数据
                 */
                $suppdays_string .= " WHEN '".$sku."' THEN '".$suppdays."'";
                $sql_string_best .= " WHEN '".$sku."' THEN '".$best."'";
                $sql_string_time .= " WHEN '".$sku."' THEN '".date('Y-m-d H:i:s')."'";

                $skus_string .= $skus_string?",'".$sku."'":"'".$sku."'";

                $daysalenum_string .= " WHEN '".$sku."' THEN '".$sku_daysalenum."'";
                $inventory_string .= " WHEN '".$sku."' THEN '".$sku_inventory."'";
                $printnum_string .= " WHEN '".$sku."' THEN '".$sku_printnum."'";
                $onwaynum_string .= " WHEN '".$sku."' THEN '".$sku_onwaynum."'";
                $safenum_string .= " WHEN '".$sku."' THEN '".$sku_safenum."'";
                $delivery_string .= " WHEN '".$sku."' THEN '".$sku_delivery."'";
            }

            if($sql_string_best and $sql_string_time and $skus_string){
                $suppdays_string = " suppdays= CASE sku ".$suppdays_string." END,";
                $sql_string_best = " bestdate= CASE sku ".$sql_string_best." END,";
                $sql_string_time = " updatatime= CASE sku ".$sql_string_time." END,";

                //$daysalenum_string = $daysalenum_string

                $inventory_string = " inventory= CASE sku ".$inventory_string." END,";

                $printnum_string = " printnum= CASE sku ".$printnum_string." END,";

                $onwaynum_string = " onwaynum= CASE sku ".$onwaynum_string." END,";

                $safenum_string = " safenum= CASE sku ".$safenum_string." END,";

                $delivery_string = " delivery= CASE sku ".$delivery_string." END";

                $skus_string = " where sku in (".$skus_string.")";

                /*                echo $update_string.
                                    $suppdays_string.
                                    $sql_string_best.
                                    $sql_string_time.
                                    $inventory_string.
                                    $printnum_string.
                                    $onwaynum_string.
                                    $safenum_string.
                                    $delivery_string.
                                    $skus_string."<br/><br/><br/><br/><br/><br/><br/><br/>";*/

                list($id,$rows) = DB::query(Database::UPDATE,$update_string.$suppdays_string.$sql_string_best.$sql_string_time.$inventory_string.$printnum_string.$onwaynum_string.$safenum_string.$delivery_string.$skus_string)
                    ->execute('newerp_tmp');
                //return $rows;
            }
        }
    }

    /**
     * 装换数据宜适应页面
     * @param array $skus
     * @return array
     */
    static function skudata(array $skus = array())
    {
        if(empty($skus)){
            return NULL;
        }
        $skus = array_values($skus);
        $goals = array();

        $targettimes = Warningtmp::get_targettime(Arr::pluck($skus,"sku"));
        $daysales    = Warningtmp::get_daysalenum(Arr::pluck($skus,"sku"));
        foreach($skus as $key => $sku){

            $goals[$key]['sku']                               = $sku['sku'];

            $goals[$key]['updatatime']                        = $sku['updatatime'];
            $goals[$key]['inventory']['actual_num']           = (float)$sku['inventory'];
            $goals[$key]['inventory']['actual_available_num'] = $goals[$key]['inventory']['actual_num']-(float)$sku['printnum'];
            $goals[$key]['inventory']['pp_num']               = (float)$sku['onwaynum'];
            $goals[$key]['supp_days']                         = (float)$sku['suppdays'];
            $goals[$key]['best']                              = (int)$sku['bestdate'];

            $goals[$key]['delivery'] = isset($sku['delivery'])?$sku['delivery']:7;
            $goals[$key]['daysales']    = isset($daysales[$sku['sku']])?$daysales[$sku['sku']]:0;
            $goals[$key]['target_time'] = isset($targettimes[$sku['sku']])?$targettimes[$sku['sku']]:14;

            /**
             * 中文名称
             */
            $name_tmp = Skusystemapi::getSkuCnname($sku['sku']);
            $goals[$key]['cnname'] = isset($name_tmp[0]['name'])?$name_tmp[0]['name']:'';
            $goals[$key]['misdays'] = $goals[$key]['delivery'] - $goals[$key]['supp_days'] ;
            $goals[$key]['suggest'] = ($goals[$key]['target_time'] + $goals[$key]['misdays'])*$goals[$key]['daysales'];

            $goals[$key]['tips'] = ($goals[$key]['best'] <=0)?"紧急":"普通";
            $goals[$key]['supp_days']  = round($goals[$key]['supp_days'] ,2);
            $goals[$key]['delivery'] = round($goals[$key]['delivery'],0);
            $goals[$key]['daysales'] = round($goals[$key]['daysales'],2);
            $goals[$key]['misdays'] = round($goals[$key]['misdays'],2);
            $goals[$key]['best'] = date("Y-m-d",strtotime($goals[$key]['best']." days"));
            $goals[$key]['suggest'] = round($goals[$key]['suggest'],2);

        }

        return $goals;
    }

    /**
     * 获取目标时间段
     * @param array $skus
     * @return array
     */
    static function get_targettime(array $skus = NULL)
    {
        if($skus){
            $sku_string = '';
            foreach($skus as $sku){
                $sku_string .= $sku_string?",'".$sku."'":"'".$sku."'";
            }

            $sql_string = 'select upper(sku) as sku,target_time as targettime  from product_attached where sku in ('.$sku_string.')';
            //echo $sql_string;exit;
            $re         = DB::query(Database::SELECT,$sql_string)
                ->execute('readonlyerp')
                ->as_array();
            $targettime = array();
            if($re){
                foreach($re as $value){
                    $print_num[$value['sku']] = $value['targettime'];
                }
            }

            return $targettime;
        }
    }

    /**
     * 处理查询

     */
    static function get_skuwarning_select(array $select = NULL)
    {
        /**
         * 查询标示为０，最佳下单日期差值小雨８的sku信息
         */
        $head_string = " SELECT upper(sku) as sku,inventory,printnum,onwaynum,bestdate,suppdays,updatatime,delivery
        FROM warning_tmp WHERE flag = 0 AND bestdate < 8";

        $add_string = "";

        if(isset($select['maxbest'])){
            $add_string .= ' AND bestdate <='.$select['maxbest'];
        }

        if(isset($select['minbest'])){
            $add_string .= ' AND bestdate >='.$select['minbest'];
        }

        /**
         * 把采购员id和sku关联起来
         */
        if(isset($select['oper'])){
            $oper_skus = Warningtmp::get_skus_by_oper($select['oper']);

            $oper_string = '';
            if($oper_skus){
                foreach($oper_skus as $oper_sku){
                    $oper_string .= $oper_string?",'".$oper_sku."'":"'".$oper_sku."'";
                }
                $oper_string = ' AND sku IN ('.$oper_string.')';
            }
        }

        if(isset($oper_string) and $oper_string){
            $add_string .= $oper_string;
        }

        /**
         * 按照最佳下单日期
         */
        $add_string .= ' order by bestdate ';

        if(isset($select['limit']) and $select['limit']>0){
            $add_string .= ' limit '.$select['limit'];
        }elseif(isset($select['limit']) and $select['limit']==0){
        }else{
            $add_string .= ' limit 50';
        }

        if(isset($select['offset']) and $select['offset']>0){
            $add_string .= ' offset '.$select['offset'];
        }
        //echo $head_string.$add_string;
        return DB::query(Database::SELECT,$head_string.$add_string)
            ->execute('newerp_tmp')
            ->as_array();
    }

    /**
     * 设置sku信息
     * @param null $sku
     */
    static function set_flag_($sku = NULL)
    {
        if($sku){
            $sql_string = " UPDATE warning_tmp set flag = -1 where sku ='".$sku."'";
            list($id,$rows) = DB::query(Database::UPDATE,$sql_string)
                ->execute('newerp_tmp');
            return $rows;
        }
    }

    /**
     * 查询sku供应商信息
     * 主要是供应商id和名称
     * @param $sku
     */
    static function get_provider_lists($sku)
    {

        if(empty($sku) or !trim($sku)){
            return;
        }

        /**
         * 全部的sku对应的供应商信息
         * 按照供应商价格排序
         */
        /*        $providers_string = "SELECT
                    trim(erp_providerproducts.iproductID) as sku,
                    erp_providerproducts.iproviderID as iproviderID ,
                    erp_provider.vProviderName as vProviderName,
                    erp_provider.payment as payment,
                    erp_providerproducts.price as price,
                    erp_providerproducts.delivery as delivery
                    from erp_providerproducts
                    join erp_provider
                    on erp_providerproducts.iproviderID=erp_provider.id
                    where erp_provider.state=2
                    and trim(erp_providerproducts.iproductID) = '".trim($sku)."'
                    order by erp_providerproducts.price";
                //echo $providers_string;exit;
                $providers = DB::query(Database::SELECT,$providers_string)
                    ->execute('readonlyerp')
                    ->as_array();*/

        $providers = Newerpclientapi::querySupplierSkuBySkus(array($sku));
        if(empty($providers)){
            return;
        }

        /**
         * 最近一次采购的供应商
         */
        $purchase_string = " SELECT supplier from  purchaseorder_details  where sku ='".$sku."'
                            and  status<70
                            and  details_date >= DATE_ADD(NOW(),INTERVAL -90 DAY)
                            order by details_date DESC limit 1";
        //echo $purchase_string;
        $supplier = DB::query(Database::SELECT,$purchase_string)
            ->execute('newerp')
            ->current();

        if(isset($supplier['supplier'])){
            $supplier = $supplier['supplier'];
        }else{
            $supplier = -1;
        }
        //print_r($supplier);
        foreach($providers as $key => $provider){
            /**
             * 最低价格，最近采购的供应商
             */
            if($provider['iproviderID']==$supplier and $providers[0]['price']==$provider['price']){
                $default         = $provider;
                $providers[$key] = $providers[0];
                $providers[0]    = $default;
            }
        }
        //print_r($providers);
        return $providers;
    }

    /**
     * 获取最新的更新信息时间
     */
    static function get_lasttime()
    {
        $lasttime_string = "SELECT updatatime FROM warning_tmp order by updatatime DESC LIMIT 1 ";
        return DB::query(Database::SELECT,$lasttime_string)
            ->execute('newerp_tmp')
            ->current();
    }


    /**
     * 获取销量大于0的sku信息
     */
    static function get_sku_by_daysales($daysale = 0)
    {
        $sku_daysales = DB::select(array(
            DB::expr('upper(sku)'),
            'sku'
        ))
            ->from('product_attached')
            ->where(DB::expr('ifnull(sku,"")'),'<>','')
            ->where('daysales','>',(int)$daysale);

        $sku_daysales = $sku_daysales->execute('readonlyerp')
            ->as_array();

        if($sku_daysales and isset($sku_daysales[0]['sku'])){
            $sku_daysales = Arr::pluck($sku_daysales,'sku');
        }
        return $sku_daysales;
    }


    /**
     *
     */
    static function get_sku_by_products(array $all = NULL)
    {
        /**
         * products_remark_2 在售状态
         * 下架：0
         * 在售：1
         * 新品测试：2
         * 暂时下架：3
         * 清货下架：4
         * 知识产权下架：5
         * 速卖通出售：6
         * 淘宝出售：7
         * 阿里出售：8
         * 工厂包材：10
         */
        $condtion_string = '';

        /**
         * 过滤在售的
         * @time29140903添加在售的
         * @time20140906添加下架，清货下架的
         */
        //        --------------------------------------kdw------------------------------------
        //        $condtion_string = ' products_remark_2 in (0,1,2,3,4,6,7) and ';
        //
        //        $sql_string1 = "select upper(products_sku) as sku
        //			from erp_products_data
        //			where ".$condtion_string."
        //			ifnull(products_sku,'') <> ''
        //			order by products_sku";
        //        /*
        //         * echo $sql_string1;
        //         */
        //        $sku_sales = DB::query(Database::SELECT,$sql_string1)
        //            ->execute('readonlyerp')
        //            ->as_array();

        $sku_sales=Skusystemapi::getSalesku();
        //        -------------------------------------kdw-end---------------------------------
        if($sku_sales and isset($sku_sales[0]['sku'])){
            $sku_sales = Arr::pluck($sku_sales,'sku');
        }

        return $sku_sales;
    }

    /**
     * 计算单个SKU的预警信息
     * @param array $sku
     * @return null
     */
    public static function get_sku_warning(array $sku=NULL)
    {
        if(empty($sku))
        {
            return NULL;
        }
        $re = array();
        $re[0] = array('sku'=>$sku[0],
                       'inventory'=>0,
                       'printnum'=>0,
                       'onwaynum'=>0,
                       'bestdate'=>90,
                       'suppdays'=>90,
                       'updatatime'=>date("Y-m-d H:i:s"),
                       'delivery'=>7);

        $inventory  = Warningtmp::get_inventory($sku); //echo "#计算实际库存".count($inventory);
        $printnum   = Warningtmp::get_print_num($sku); //echo "#计算订单占用库存".count($printnum);
        $onwaynum   = Warningtmp::get_onway_num($sku); //echo "#计算在途库存".count($onwaynum);
        $safenum    = Warningtmp::get_safenum($sku); //echo "#计算安全库存".count($safenum);
        $daysalenum = Warningtmp::get_daysalenum($sku); //echo "#计算日均销量库存".count($daysalenum);
        $delivery = Warningtmp::get_delivery($sku);

        $re[0]['inventory'] = isset($inventory[$sku[0]])?$inventory[$sku[0]]:0;unset($inventory);
        $re[0]['printnum'] = isset($printnum[$sku[0]])?$printnum[$sku[0]]:0;unset($printnum);
        $re[0]['onwaynum'] = isset($onwaynum[$sku[0]])?$onwaynum[$sku[0]]:0;unset($onwaynum);
        $re[0]['safenum'] = isset($safenum[$sku[0]])?$safenum[$sku[0]]:3;unset($safenum);
        $re[0]['daysales'] = isset($daysalenum[$sku[0]])?$daysalenum[$sku[0]]:0;unset($daysalenum);
        $re[0]['delivery'] = isset($delivery[$sku[0]])?$delivery[$sku[0]]:7;unset($delivery);

        if($re[0]['daysales']>0)
        {
            $re[0]['suppdays'] = ((float)$re[0]['inventory'] - (float)$re[0]['printnum'] + (float)$re[0]['onwaynum'])/((float)$re[0]['daysales']) - (float)$re[0]['safenum'];
        }

        $re[0]['bestdate'] = $re[0]['suppdays'] - $re[0]['delivery'];

        return $re;
    }



    public static function i_array_column($input, $columnKey, $indexKey=null)
    {
        if(!function_exists('array_column')){
            $columnKeyIsNumber  = (is_numeric($columnKey))?true:false;
            $indexKeyIsNull            = (is_null($indexKey))?true :false;
            $indexKeyIsNumber     = (is_numeric($indexKey))?true:false;
            $result                         = array();
            foreach((array)$input as $key=>$row){
                if($columnKeyIsNumber){
                    $tmp= array_slice($row, $columnKey, 1);
                    $tmp= (is_array($tmp) && !empty($tmp))?current($tmp):null;
                }else{
                    $tmp= isset($row[$columnKey])?$row[$columnKey]:null;
                }
                if(!$indexKeyIsNull){
                    if($indexKeyIsNumber){
                        $key = array_slice($row, $indexKey, 1);
                        $key = (is_array($key) && !empty($key))?current($key):null;
                        $key = is_null($key)?0:$key;
                    }else{
                        $key = isset($row[$indexKey])?$row[$indexKey]:0;
                    }
                }
                $result[$key] = $tmp;
            }
            return $result;
        }else{
            return array_column($input, $columnKey, $indexKey);
        }
    }
}


